﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace Common
{
    /// <summary>
    /// Mysql的分页
    /// </summary>
    public class GetPagerForMysql
    {
        public static DataSet GetPagerDataforDataSet(string connStr, int currentpageindex, int pagesize, string tablename, string select, string where, string orderby, ref int count)
        {
            MySqlConnection conn = new MySqlConnection(connStr);
            MySqlCommand myCommand = conn.CreateCommand();
            myCommand.Connection = conn;
            try
            {
                conn.Open();
                myCommand.CommandText = "select count(*) from " + tablename + " where " + where;
                count = int.Parse(myCommand.ExecuteScalar().ToString());
                if (count == 0)
                {
                    return null;
                }
                else
                {
                    int start = (currentpageindex - 1) * pagesize;
                    int size = pagesize;
                    myCommand.CommandText = "select " + select + " from " + tablename +
                       " where " + where + " order by " + orderby + "  limit " + start + "," + size;


                    MySqlDataAdapter ada = new MySqlDataAdapter(myCommand);
                    DataSet ds = new DataSet();
                    ada.Fill(ds);
                    return ds;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogHelper(ex.Message + ",GetPagerforSqlServer.GetPagerData(where)", System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
            return null;
        }
        public static DataSet GetPagerDataforDataSet(string connStr, int currentpageindex, int pagesize, string tablename, string select, string where, string orderby, ref int count, params object[] parameters)
        {
            MySqlConnection conn = new MySqlConnection(connStr);
            MySqlCommand myCommand = conn.CreateCommand();
            myCommand.Connection = conn;
            try
            {
                conn.Open();
                myCommand.CommandText = "select count(*) from " + tablename + " where " + where;
                count = int.Parse(myCommand.ExecuteScalar().ToString());
                if (count == 0)
                {
                    return null;
                }
                else
                {
                    int start = (currentpageindex - 1) * pagesize;
                    int size = pagesize;
                    myCommand.CommandText = "select " + select + " from " + tablename +
                       " where " + where + " order by " + orderby + "  limit " + start + "," + size;
                    myCommand.Parameters.AddRange(parameters);

                    MySqlDataAdapter ada = new MySqlDataAdapter(myCommand);
                    DataSet ds = new DataSet();
                    ada.Fill(ds);
                    return ds;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogHelper(ex.Message + ",GetPagerforSqlServer.GetPagerData(where)", System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
            return null;
        }

    }

}
